package cn.iocoder.yudao.framework.base.common.excel.convert;

import java.io.*;
import java.util.Arrays;
import java.util.Objects;

import cn.hutool.core.util.EscapeUtil;
import com.alibaba.excel.util.StringUtils;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.*;


public class ExcelToHtmlConverter {
    public enum Type {
        XLS, XLSX
    }

    /**
     * 提取属性信息
     */
    boolean attribute = true;
    /**
     * 平铺合并的单元格
     */
    boolean flat = false;


    public ExcelToHtmlConverter(boolean attribute, boolean flat) {
        this.attribute = attribute;
        this.flat = flat;
    }

    public void convertToHtml(File inputFile, File outputFile) throws IOException {

        // Create a workbook object from the input file
        Workbook workbook = getWorkbook(inputFile);

        // Write the HTML to the output file
        Writer writer = new BufferedWriter(new FileWriter(outputFile));
        writer.write(getHtml(workbook));
        writer.close();

        // Close the workbook
        workbook.close();
    }

    public String convertToHtml(File inputFile) throws IOException {

        // Create a workbook object from the input file
        Workbook workbook = getWorkbook(inputFile);

        String html = getHtml(workbook);

        // Close the workbook
        workbook.close();
        return html;
    }

    public Workbook getWorkbook(File inputFile) throws IOException {
        // Check if the file is an XLS or XLSX file
        Workbook workbook;
        if (inputFile.getName().endsWith(".xls")) {
            workbook = getWorkbook(new FileInputStream(inputFile), Type.XLS);
        } else if (inputFile.getName().endsWith(".xlsx")) {
            workbook = getWorkbook(new FileInputStream(inputFile), Type.XLSX);
        } else {
            throw new IllegalArgumentException("The specified file is not an Excel file");
        }
        return workbook;
    }

    public Workbook getWorkbook(InputStream inputStream, Type type) throws IOException {
        // Check if the file is an XLS or XLSX file
        Workbook workbook;
        if (Type.XLS.equals(type)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (Type.XLSX.equals(type)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("The specified file is not an Excel file");
        }
        return workbook;
    }

    protected String cellToString(Cell cell) {
        if (cell != null) {
            CellType cellType = cell.getCellType();
            if (cellType == CellType.NUMERIC) {
                return NumberToTextConverter.toText(cell.getNumericCellValue());
            }

             return EscapeUtil.escapeHtml4(cell.toString());
        }
        return "";
    }

    public String getHtml(Workbook workbook) {
        StringBuilder htmlBuilder = new StringBuilder();
        htmlBuilder.append("<html><head><style>");
        htmlBuilder.append(getStyle());
        htmlBuilder.append("</style></head><body>");
        // Loop through each sheet in the workbook

        Sheet sheet = workbook.getSheetAt(0);
        htmlBuilder.append("<table>");
        // Loop through each row in the sheet
        int rowCount = sheet.getLastRowNum() + 1;
        int[] colCounts = getColCounts(sheet, rowCount);
        final int columnCount = Arrays.stream(colCounts).max().getAsInt();
        String[][] table = new String[rowCount][columnCount];
        for (int r = 0; r < rowCount; r++) {
            Row row = sheet.getRow(r);

            // Loop through each cell in the row

            for (int c = 0; c < columnCount; c++) {
                Cell cell = row.getCell(c);
                if (!isMergedRegion(sheet, r, c)) {
                    String attr = this.attribute ? toAttribute(workbook, cell) : "";
                    StringBuilder builder = new StringBuilder().append("<td class=\"cell\"").append(attr).append(">");
                    if (cell != null) {
                        builder.append(cellToString(cell));
                    }
                    builder.append("</td>");
                    table[r][c] = builder.toString();

                }
            }
        }
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            cellRange(workbook, sheet, mergedRegion, table, flat);
        }
        for (String[] row : table) {
            htmlBuilder.append("<tr>");
            for (String cell : row) {
                if (cell != null) {
                    htmlBuilder.append(cell);
                }
            }
            htmlBuilder.append("</tr>");
        }
        htmlBuilder.append("</table>");

        htmlBuilder.append("</body></html>");
        return htmlBuilder.toString();
    }

    protected void cellRange(Workbook workbook, Sheet sheet, CellRangeAddress mergedRegion, String[][] table, boolean flat) {
        int rowspan = getRowspan(sheet, mergedRegion.getFirstRow(), mergedRegion.getFirstColumn());
        int colspan = getColspan(sheet, mergedRegion.getFirstRow(), mergedRegion.getFirstColumn());
        StringBuilder text = new StringBuilder();
        Cell firstCell = sheet.getRow(mergedRegion.getFirstRow()).getCell(mergedRegion.getFirstColumn());

        mergedRegion.iterator().forEachRemaining(cellAddress -> {
            Cell cell = sheet.getRow(cellAddress.getRow()).getCell(cellAddress.getColumn());
            if (cell != null) {
                text.append(cellToString(cell));
            }
        });
        String attr = "";
        if (this.attribute) {
            attr = toAttribute(workbook, firstCell);
            StringBuilder builder = new StringBuilder();

            builder.append(" ").append("data-colspan-rowspan").append("=\"").append(colspan).append(",").append(rowspan).append("\"");
            attr += builder.toString();
        }
        final String attrStr = attr;

        if (flat) {
            mergedRegion.iterator().forEachRemaining(cellAddress -> {

                StringBuilder builder = new StringBuilder().append("<td class=\"cell\"").append(attrStr).append(">");
                builder.append(text.toString());
                builder.append("</td>");
                table[cellAddress.getRow()][cellAddress.getColumn()] = builder.toString();
            });
        } else {
            StringBuilder builder = new StringBuilder();

            builder.append("<td colspan=\"" + colspan + "\" rowspan=\"" + rowspan + "\" class=\"cell\"").append(attrStr).append(">");
            builder.append(text.toString());

            builder.append("</td>");
            table[mergedRegion.getFirstRow()][mergedRegion.getFirstColumn()] = builder.toString();
        }

    }

    protected String toAttribute(Workbook workbook, Cell cell) {
        StringBuilder builder = new StringBuilder();
        if (cell == null) {
            return builder.toString();
        }

        CellStyle cellStyle = cell.getCellStyle();
        if (cellStyle != null) {
            addAttribute(builder, "data-background-color", toColor(workbook, cellStyle.getFillBackgroundColor()));
            addAttribute(builder, "data-foreground-color", toColor(workbook, cellStyle.getFillForegroundColor()));

            addAttribute(builder, "data-alignment", cellStyle.getAlignment().toString());
            addAttribute(builder, "data-vertical-alignment", cellStyle.getVerticalAlignment().toString());
            addAttribute(builder, "data-border-bottom-color", toColor(workbook, cellStyle.getBottomBorderColor()));
            addAttribute(builder, "data-border-left-color", toColor(workbook, cellStyle.getLeftBorderColor()));
            addAttribute(builder, "data-border-right-color", toColor(workbook, cellStyle.getRightBorderColor()));
            addAttribute(builder, "data-border-top-color", toColor(workbook, cellStyle.getTopBorderColor()));
            int fontIndexAsInt = cellStyle.getFontIndexAsInt();
            Font font = workbook.getFontAt(fontIndexAsInt);
            if (font != null) {
                addAttribute(builder, "data-font-name", font.getFontName());
                addAttribute(builder, "data-font-bold", font.getBold());
                addAttribute(builder, "data-font-height", font.getFontHeight());
                addAttribute(builder, "data-font-italic", font.getItalic());
                addAttribute(builder, "data-font-underline", toUnderline(font.getUnderline()));
                addAttribute(builder, "data-font-color", toColor(workbook, font.getColor()));
            }

        }
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();
        addAttribute(builder, "data-row", rowIndex);
        addAttribute(builder, "data-col", columnIndex);


        return builder.toString();

    }

    /**
     * * @see #U_NONE
     * * @see #U_SINGLE
     * * @see #U_DOUBLE
     * * @see #U_SINGLE_ACCOUNTING
     * * @see #U_DOUBLE_ACCOUNTING
     *
     * @param value
     * @return
     */
    private String toUnderline(byte value) {
        if (value == Font.U_NONE) {
            return "";
        } else if (value == Font.U_SINGLE) {
            return "U_SINGLE";
        } else if (value == Font.U_DOUBLE) {
            return "U_DOUBLE";
        } else if (value == Font.U_DOUBLE) {
            return "U_DOUBLE";
        } else if (value == Font.U_SINGLE_ACCOUNTING) {
            return "U_SINGLE_ACCOUNTING";
        } else if (value == Font.U_DOUBLE_ACCOUNTING) {
            return "U_DOUBLE_ACCOUNTING";
        }
        return "";
    }

    private void addAttribute(StringBuilder builder, String name, Object value) {
        if (value != null) {
            String string = Objects.toString(value);
            if (StringUtils.isNotBlank(string)) {
                builder.append(" ").append(name).append("=\"").append(string).append("\"");

            }
        }

    }

    private String toColor(Workbook workbook, short color) {
        if (workbook instanceof HSSFWorkbook) {
            HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette();
            HSSFColor object = palette.getColor(color);
            if (object == null) {
                return "";
            } else {
                return object.getHexString();
            }


        }

        return String.valueOf(color);


    }

    private int[] getColCounts(Sheet sheet, int rowCount) {
        int[] colCounts = new int[rowCount];
        for (int r = 0; r < rowCount; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                colCounts[r] = 0;
            } else {
                int columnCount = row.getLastCellNum();
                int colIndex = 0;
                for (int c = 0; c < columnCount; c++) {
                    if (isMergedRegion(sheet, r, colIndex)) {
                        colIndex += getColspan(sheet, r, colIndex);
                    } else {
                        colIndex++;
                    }
                }
                colCounts[r] = columnCount;
            }
        }
        return colCounts;
    }

    private boolean isMergedRegion(Sheet sheet, int rowIndex, int columnIndex) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(rowIndex, columnIndex)) {
                return true;
            }
        }
        return false;
    }

    private int getRowspan(Sheet sheet, int rowIndex, int columnIndex) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstColumn() == columnIndex && mergedRegion.getFirstRow() == rowIndex) {
                return mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;
            }
        }
        return 1;
    }

    private int getColspan(Sheet sheet, int rowIndex, int columnIndex) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstColumn() == columnIndex && mergedRegion.getFirstRow() == rowIndex) {
                return mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;
            }
        }
        return 1;
    }

    private String getStyle() {
        return ".cell { border: 1px solid black; }";
    }

//    public static void main(String[] args) throws IOException {
//        File input = new File("C:\\Users\\Administrator\\Documents\\WeChat Files\\duoshow\\FileStorage\\File\\2023-05\\织布各机型工艺模板汇总2023-4-14(2).xls");
//        File output = new File("C:\\Users\\Administrator\\Documents\\WeChat Files\\duoshow\\FileStorage\\File\\2023-05\\b.html");
//        ExcelToHtmlConverter converter = new ExcelToHtmlConverter(true, true);
//        converter.convertToHtml(input, output);
//    }
}
